﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Data.Models;
using System.Collections.Generic;
using AccessControl.Contracts.Models;

namespace AccessControl.DataManagers.Tables.Managers
{
    class EventsManager : TableManager<IEvent>
    {
        #region Fields

        private SqlDataAdapter _adapter;
        private static EventsManager s_Instance = null;

        private static readonly string COL_1_ID = "ID";
        private static readonly string COL_2_TIME = "TIME";
        private static readonly string COL_3_SENSOR_ID = "SENSOR_ID";
        private static readonly string COL_4_HANDLED = "HANDLED";
        private static readonly string COL_5_HANDLING_OPERATOR = "HANDLING_OPERATOR";
        private static readonly string COL_6_DESCRIPTION = "DESCRIPTION";

        #endregion

        #region Private Constructor

        /// <summary>
        /// Prevents a default instance of the <see cref="EventsManager"/> class from being created.
        /// </summary>
        private EventsManager()
        {
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Gets the instance.
        /// </summary>
        public static EventsManager Instance
        {
            get
            {
                lock (typeof(EventsManager))
                {
                    if (s_Instance == null)
                        s_Instance = new EventsManager();

                    return s_Instance;
                }
            }
        }

        #endregion

        #region Table Manager Overrides

        /// <summary>
        /// Creates using the specified connection.
        /// </summary>
        /// <param name="connection">The connection.</param>
        public override void Create(SqlConnection connection)
        {
            /* Create the maps table */
            SqlCommand command = connection.CreateCommand();
            command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} BIGINT, {3} INT, {4} TEXT, {5} TEXT, {6} TEXT)", 
                                                TablesNames.EVENTS, COL_1_ID, COL_2_TIME, COL_3_SENSOR_ID, COL_4_HANDLED, COL_5_HANDLING_OPERATOR, COL_6_DESCRIPTION);
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Initializes this instance.
        /// </summary>
        public override void Initialize(SqlConnection connection)
        {
			/* Create the adapter */
			_adapter = new SqlDataAdapter();
			/* Create the commands */
			_adapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.EVENTS), connection);

			_adapter.InsertCommand = new SqlCommand(string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}, {5}, {6}) VALUES (@{1}, @{2}, @{3}, @{4}, @{5}, @{6})",
													 TablesNames.EVENTS, COL_1_ID, COL_2_TIME, COL_3_SENSOR_ID, COL_4_HANDLED, COL_5_HANDLING_OPERATOR, COL_6_DESCRIPTION), connection);

			_adapter.UpdateCommand = new SqlCommand(string.Format("UPDATE {0} SET {2} = @{2}, {3} = @{3}, {4} = @{4}, {5} = @{5}, {6} = @{6} WHERE {1} = @{1}",
													TablesNames.EVENTS, COL_1_ID, COL_2_TIME, COL_3_SENSOR_ID, COL_4_HANDLED, COL_5_HANDLING_OPERATOR, COL_6_DESCRIPTION), connection);

			_adapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}", TablesNames.EVENTS, COL_1_ID), connection);
        }

        /// <summary>
        /// Gets the list.
        /// </summary>
        /// <returns></returns>
        public override bool FillList(IList<IEvent> items)
        {
			List<ISensor> sensors = new List<ISensor>();

			/* Fill the sensors list */
			SensorsManager.Instance.FillList(sensors);

			using (SqlDataReader reader = _adapter.SelectCommand.ExecuteReader())
			{
				FillEventsFromReader(reader, items, sensors);
				reader.Close();
			}
			
			return true;
        }

		/// <summary>
		/// Fills the list.
		/// </summary>
		/// <param name="items">The items.</param>
		/// <param name="startTime">The start time.</param>
		/// <param name="endTime">The end time.</param>
		/// <returns></returns>
		public bool FillList(IList<IEvent> items, DateTime startTime, DateTime endTime)
		{
			List<ISensor> sensors = new List<ISensor>();

			/* Fill the sensors list */
			SensorsManager.Instance.FillList(sensors);

			SqlCommand selectCommand = new SqlCommand();
			selectCommand.CommandText = string.Format("SELECT * FROM {0} WHERE {1} >= {2} AND {1} <= {3}", TablesNames.EVENTS, COL_2_TIME,
																										   startTime.ToFileTime(), endTime.ToFileTime());

			using (SqlDataReader reader = _adapter.SelectCommand.ExecuteReader())
			{
				FillEventsFromReader(reader, items, sensors);
				reader.Close();
			}

			return true;
		}

		/// <summary>
		/// Fills the list.
		/// </summary>
		/// <param name="items">The items.</param>
		/// <param name="startTime">The start time.</param>
		/// <param name="endTime">The end time.</param>
		/// <param name="sensors">The sensors.</param>
		/// <returns></returns>
		public bool FillList(IList<IEvent> items, DateTime startTime, DateTime endTime, IList<int> sensorIds)
		{
			bool bIsFirstID = true;
			List<ISensor> sensors = new List<ISensor>();

			SqlCommand selectCommand = new SqlCommand() { Connection = _adapter.SelectCommand.Connection };
			selectCommand.CommandText = string.Format("SELECT * FROM {0} WHERE {1} >= {2} AND {1} <= {3}", TablesNames.EVENTS, COL_2_TIME,
																										   startTime.ToFileTime(), endTime.ToFileTime());

			if (sensors.Count > 0)
			{
				selectCommand.CommandText += " AND (";
				foreach (int id in sensorIds)
				{
					if (!bIsFirstID)
						selectCommand.CommandText += " OR ";

					selectCommand.CommandText += string.Format("{0} = {1}", COL_3_SENSOR_ID, id);	
					bIsFirstID = false;
				}
				selectCommand.CommandText += ")";
			}

			/* Fill the sensors list */
			SensorsManager.Instance.FillList(sensors);

			using (SqlDataReader reader = selectCommand.ExecuteReader())
			{
				FillEventsFromReader(reader, items, sensors);
				reader.Close();
			}

			return true;
		}

        /// <summary>
        /// Adds the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Add(IEvent item)
        {
			_adapter.InsertCommand.Parameters.Clear();

			/* Set the item id */
			item.Id = GetHighestId(_adapter.SelectCommand.Connection, TablesNames.EVENTS, COL_1_ID) + 1;

			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_TIME, item.EventTime.ToFileTime()));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_3_SENSOR_ID, item.AssociatedSensor.Id));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_4_HANDLED, item.Handled.ToString()));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_5_HANDLING_OPERATOR, item.HandlingOperator));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_6_DESCRIPTION, item.Description));

			return (_adapter.InsertCommand.ExecuteNonQuery() != 0);
        }

        /// <summary>
        /// Updates the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Update(IEvent item)
        {
			_adapter.UpdateCommand.Parameters.Clear();
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_2_TIME, item.EventTime.ToFileTime()));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_3_SENSOR_ID, item.AssociatedSensor.Id));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_4_HANDLED, item.Handled.ToString()));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_5_HANDLING_OPERATOR, item.HandlingOperator));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_6_DESCRIPTION, item.Description));

			return (_adapter.UpdateCommand.ExecuteNonQuery() != 0);
        }

        /// <summary>
        /// Deletes the specified item.
        /// </summary>
        /// <param name="item">The item.</param>
		public override bool Delete(IEvent item)
        {
			_adapter.DeleteCommand.Parameters.Clear();
			_adapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));

			return (_adapter.DeleteCommand.ExecuteNonQuery() != 0);
        }

        #endregion

		#region Private Members

		void FillEventsFromReader(SqlDataReader reader, IList<IEvent> items, IList<ISensor> sensors)
		{
			int sensorId;
			Event myEvent;			

			while (reader.Read())
			{
				myEvent = new Event()
				{
					Id = reader.GetInt32(0),
					Description = reader.GetString(5),
					EventTime = DateTime.FromFileTime(reader.GetInt64(1)),
					Handled = Convert.ToBoolean(reader.GetString(3)),
					HandlingOperator = reader.GetString(4)
				};

				/* Now find the matching sensor */
				sensorId = reader.GetInt32(2);
				foreach (ISensor sensor in sensors)
				{
					if (sensor.Id == sensorId)
					{
						myEvent.AssociatedSensor = sensor;
						break;
					}
				}

				items.Add(myEvent);
			}
		}

		#endregion
	}
}
